﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Web.Admin.Report
{
    public partial class RWBStorageBalance : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
               
            }
        }
        private void DataBind()
        {


            //获取存粮信息
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT C.ID as WBID, C.strName AS WBName,D.ID as TimeID, D.strName AS  TimeName ,E.ID as VarietyID, E.strName AS  VarietyName,SUM(StorageNumber) AS StorageNumber ,A.Price_ShiChang,SUM(StorageNumber)*Price_ShiChang AS TotalPrice");
            strSql.Append("  FROM Dep_StorageInfo A INNER JOIN dbo.Depositor B ON A.AccountNumber=B.AccountNumber");
            strSql.Append("   INNER JOIN dbo.WB C ON B.WBID=C.ID");
            strSql.Append("  INNER JOIN dbo.StorageTime D ON A.TimeID=D.ID");
            strSql.Append("  INNER JOIN dbo.StorageVariety E ON A.VarietyID=E.ID");
            strSql.Append("  where 1=1 and C.ISSimulate=0");
            if (common.ISHQWB(Session["WB_ID"]))
            {
                if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
                {
                    strSql.Append("   AND C.ID = " + Request.Form["QWBID"].ToString());
                }
            }
            else {
                strSql.Append("   AND C.ID = " +Session["WB_ID"].ToString());
            }
          

            strSql.Append("   GROUP BY C.ID, C.strName ,D.ID,D.strName,E.ID,E.strName,A.Price_ShiChang");
            strSql.Append("  ");


            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
            Repeater1.DataSource = dt;
            Repeater1.DataBind();



         


        }



        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        {
           

            DataBind();

        }



        DataTable GetoutputTable()
        {
            //获取存粮信息
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT C.strName AS WBID,D.strName AS  TimeID ,E.strName AS  VarietyID,SUM(StorageNumber) AS StorageNumber ,A.Price_ShiChang,SUM(StorageNumber)*Price_ShiChang AS TotalPrice");
            strSql.Append("  FROM Dep_StorageInfo A INNER JOIN dbo.Depositor B ON A.AccountNumber=B.AccountNumber");
            strSql.Append("   INNER JOIN dbo.WB C ON B.WBID=C.ID");
            strSql.Append("  INNER JOIN dbo.StorageTime D ON A.TimeID=D.ID");
            strSql.Append("  INNER JOIN dbo.StorageVariety E ON A.VarietyID=E.ID");
            strSql.Append("  where 1=1 and C.ISSimulate=0");
            if (common.ISHQWB(Session["WB_ID"]))
            {
                if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
                {
                    strSql.Append("   AND C.ID = " + Request.Form["QWBID"].ToString());
                }
            }
            else
            {
                strSql.Append("   AND C.ID = " + Session["WB_ID"].ToString());
            }

            strSql.Append("   GROUP BY C.strName ,D.strName,E.strName,A.Price_ShiChang");
            strSql.Append("  ");

            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
            if (dt == null || dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return SQLHelper.ExecuteDataTable(strSql.ToString());
            }
        }

        private void outputExcel()
        {
            DataTable dt = GetoutputTable();
            if (dt == null || dt.Rows.Count == 0)
            {
                Fun.Alert("请先检索要查询的信息后在导出！");
                return;
            }

            string fileName = "存粮报表" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";

            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            string colHeaders = "", ls_item = "", ls_Bottom = "";

            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;

            colHeaders += "网点\t存期\t存储产品\t存入量\t存入价\t折合现金\n";
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息

            //逐行处理数据  
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加\n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";

            }


            resp.End();
        }

        protected void btnOutPut_Click(object sender, EventArgs e)
        {
            outputExcel();
        }
    }
}